The idea behind this was simply to bind rows of every spreadsheet and check for warnings and errors. If column types were different errors had to be risen and formats would be subject of warnings.
The first step of checking the spreadsheets consisted in trying to read all of them and binding their rows. By doing this, if the column type was not the same, we then had to set the columns with the same type. Also, by reading each spreadsheet, read_excel may warns us if something is wrong.
5 Problem solving
We defined for each column over the 5 spreadsheets which type all of them were. We populated a table directly in Excel and then we read using the following commands:
Code
col_types <-list()path <-"support/column_types.xlsx"sheet_names <- readxl::excel_sheets(path)for (i in1:length(sheet_names)) { col_types[[sheet_names[i]]] <- readxl::read_excel(path = path, sheet = i)}# Showing an example of the outputcol_types[1]
$Underpasses
# A tibble: 17 × 2
Column Type
<chr> <chr>
1 Infrastructure_type text
2 Structure_ID text
3 Structure_type text
4 Structure_cell text
5 Structure_shape text
6 Structure_photo text
7 Structure_age numeric
8 Structure_height numeric
9 Structure_length numeric
10 Structure_width numeric
11 Waterbody_width numeric
12 Latitude numeric
13 Longitude numeric
14 UTM Zone text
15 X (Easting) numeric
16 Y (Northing) numeric
17 Datum text
The following process has to be repeated for all the spreadsheets, however here we illustrate using the “Underpasses” spreadsheet. In order to collect the messages provided by R we adapted the read_excel function with a purrr::quietly function, that is more appropriate to show warnings and messages for each file/author. We enchain the customized read_sheet function that provides the full paths of all .xlsx files available to the purrr::quietly function.
In the sequence, we are able to collect the warnings (in this case, under_warns), check and correct them directly on the Excel files. The majority of the errors are misspelled text, decimal markers, date and time separators and so on…
After we zeroing the warnings, we are able to check if R successfully bind the rows from each author. If no warnings or errors are raised, it means that we reached our goal.